<?php

namespace App\Models;

use Dcat\Admin\Traits\HasDateTimeFormatter;
use Illuminate\Database\Eloquent\Model;

class ProjectMeetingInvitationSpeaker extends Model
{
    use HasDateTimeFormatter;
    const CREATED_AT = 'create_time';
    const UPDATED_AT = 'update_time';
    public $timestamps = true;

    protected $table = 'eb_project_meeting_invitation_speaker';

    protected $hidden = [
        // 'email',
        'speaker_email',
    ];

    /**
     * 会议相关
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-11-25
     * @return void
     */
    public function meeting()
    {
        return $this->hasOne(ProjectMeeting::class, 'id', 'project_meeting_id');
    }

    /**
     * 反向绑定到项目
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-11-25
     * @return void
     */
    public function project()
    {
        return $this->belongsTo(Project::class, 'id', 'project_id');
    }

    // 会议讲者关联讲者表，获取讲者信息
    public function speakerDetail()
    {
        return $this->hasOne(Speaker::class, 'id', 'speaker_id');
    }

    // 资料上传
    public function uploadData()
    {
        return $this->hasOne(ProjectMeetingUploadData::class, 'invitation_speaker_id', 'id');
    }

    public function uploadDetail()
    {
        return $this->hasOne(ProjectMeetingUploadData::class, 'invitation_speaker_id', 'id');
    }

    /**
     * 常规邮件&发送状态
     * 线上-线上会议&测试
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-22
     * @return void
     */
    public function meetingOnline()
    {
        return $this->hasOne(ProjectMeetingOnlineConference::class, 'invitation_speaker_id', 'id');
    }

    /**
     * 邮件邀请记录
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-11-25
     * @return void
     */
    public function emailRecord()
    {
        return $this->hasMany(ProjectMeetingInvitationEmailRecord::class, 'project_meeting_invitation_speaker_id', 'id');
    }

    /**
     * 关联外讲库讲者表
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-22
     * @return void
     */
    public function speaker()
    {
        return $this->hasOne(Speaker::class, 'id', 'speaker_id');
    }

    /**
     * 常规邮件&发送状态
     * 索要讲者简历+头像
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-22
     * @return void
     */
    public function speakerResume()
    {
        return $this->hasOne(ProjectMeetingSpeakerResume::class, 'invitation_speaker_id', 'id');
    }

    /**
     * 常规邮件&发送状态
     * 索要讲者付款信息+护照
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-22
     * @return void
     */
    public function speakerPassport()
    {
        return $this->hasOne(ProjectMeetingSpeakerPassport::class, 'invitation_speaker_id', 'id');
    }

    /**
     * 常规邮件&发送状态
     * 签订协议
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-22
     * @return void
     */
    public function signAgreement()
    {
        return $this->hasOne(ProjectMeetingSignAgreement::class, 'invitation_speaker_id', 'id');
    }

    /**
     * 常规邮件&发送状态
     * PPT要求及其返回时间
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-22
     * @return void
     */
    public function pptReturnDeadline()
    {
        return $this->hasOne(ProjectMeetingPptReturnDeadline::class, 'invitation_speaker_id', 'id');
    }

    /**
     * 常规邮件&发送状态
     * PPT跟进
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-22
     * @return void
     */
    public function pptFollowUp()
    {
        return $this->hasOne(ProjectMeetingPptFollowUp::class, 'invitation_speaker_id', 'id');
    }

    /**
     * 常规邮件&发送状态
     * 翻译PPT给讲者
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-22
     * @return void
     */
    public function pptTranslate()
    {
        return $this->hasOne(ProjectMeetingPptTranslate::class, 'invitation_speaker_id', 'id');
    }


    /**
     * 常规邮件&发送状态
     * 翻译讨论问题给讲者
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-22
     * @return void
     */
    public function translateToLecturer()
    {
        return $this->hasOne(ProjectMeetingPptTranslateProblem::class, 'invitation_speaker_id', 'id');
    }

    /**
     * 关联 cuc
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-21
     * @return void
     */
    public function cuc()
    {
        return $this->hasOne(ProjectMeetingCuc::class, 'invitation_speaker_id', 'id');
    }

    /**
     * 关联 劳务支付
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-21
     * @return void
     */
    public function charge()
    {
        return $this->hasOne(ProjectMeetingServiceCharge::class, 'invitation_speaker_id', 'id');
    }

    /**
     * 常规邮件&发送状态
     * 线上-可编辑日程
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-22
     * @return void
     */
    public function meetingSchedule()
    {
        return $this->hasOne(ProjectMeetingSchedule::class, 'invitation_speaker_id', 'id');
    }

    /**
     * 常规邮件&发送状态
     * 线上-会后感谢信
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-22
     * @return void
     */
    public function meetingAfter()
    {
        return $this->hasOne(ProjectMeetingAfterTheMeeting::class, 'invitation_speaker_id', 'id');
    }


    /**==========================================线下====================================================== */
    /**
     * 常规邮件&发送状态
     * 线下-签证计划
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-26
     * @return void
     */
    public function visaProgram()
    {
        return $this->hasOne(ProjectMeetingVisaProgram::class, 'invitation_speaker_id', 'id');
    }

    /**
     * 常规邮件&发送状态
     * 线下-签证包
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-26
     * @return void
     */
    public function visaPackage()
    {
        return $this->hasOne(ProjectMeetingVisaPackage::class, 'invitation_speaker_id', 'id');
    }

    /**
     * 常规邮件&发送状态
     * 线下-邀请函
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-26
     * @return void
     */
    public function invitationLetter()
    {
        return $this->hasOne(ProjectMeetingInvitationLetter::class, 'invitation_speaker_id', 'id');
    }

    /**
     * 常规邮件&发送状态
     * 线下-邀请函邮寄单号
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-26
     * @return void
     */
    public function invitationLetterNumber()
    {
        return $this->hasOne(ProjectMeetingInvitationLetterNumber::class, 'invitation_speaker_id', 'id');
    }

    /**
     * 常规邮件&发送状态
     * 线下-常旅客号
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-26
     * @return void
     */
    public function frequentFlyer()
    {
        return $this->hasOne(ProjectMeetingFrequentFlyer::class, 'invitation_speaker_id', 'id');
    }

    /**
     * 常规邮件&发送状态
     * 线下-发送喜好问卷
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-26
     * @return void
     */
    public function meetingQuestions()
    {
        return $this->hasOne(ProjectMeetingQuestionnaire::class, 'invitation_speaker_id', 'id');
    }

    /**
     * 常规邮件&发送状态
     * 线下-最终行程
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-26
     * @return void
     */
    public function finalTravel()
    {
        return $this->hasOne(ProjectMeetingFinalTravel::class, 'invitation_speaker_id', 'id');
    }

    /**
     * 常规邮件&发送状态
     * 线下-中国签证扫描页
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-26
     * @return void
     */
    public function chinaVsiaScan()
    {
        return $this->hasOne(ProjectMeetingChinaVisaScan::class, 'invitation_speaker_id', 'id');
    }

    /**
     * 常规邮件&发送状态
     * 线下-接机安排和温馨提示
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-26
     * @return void
     */
    public function pickUpAndTips()
    {
        return $this->hasOne(ProjectMeetingPickupAndTips::class, 'invitation_speaker_id', 'id');
    }

    /**
     * 常规邮件&发送状态
     * 线下-活动感谢信+询问报销+出入境扫描件
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-26
     * @return void
     */
    public function scan()
    {
        return $this->hasOne(ProjectMeetingLetterReimbursementScan::class, 'invitation_speaker_id', 'id');
    }

    /**
     * 常规邮件&发送状态
     * 线下-报销单据
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-26
     * @return void
     */
    public function reimbursement()
    {
        return $this->hasOne(ProjectMeetingReimbursement::class, 'invitation_speaker_id', 'id');
    }

    /**
     * 常规邮件&发送状态
     * 线下-结算单
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-08-26
     * @return void
     */
    public function statement()
    {
        return $this->hasOne(ProjectMeetingFinalStatement::class, 'invitation_speaker_id', 'id');
    }



    /**
     * 只查没有被删除的作用域
     *
     * @param  \Illuminate\Database\Eloquent\Builder  $query
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeDeleteTime($query)
    {
        return $query->whereNull('delete_time');
    }

    /**
     * 获取项目会议讲者信息
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-11-22
     * @param [type] $payload
     * @return void
     */
    public function getProjectMeetingInvitationSpeakerList($payload)
    {
        $project_id = $payload['project_id'];
        $project_meeting_id = $payload['project_meeting_id'];
        $where = function ($query) use ($payload, $project_id, $project_meeting_id) {

            $query->where('project_id', $project_id);
            $query->where('project_meeting_id', $project_meeting_id);
            // $query->where('user_id', $userId);
            $query->where('is_display', 1);


            if (isset($payload['speaker_name']) && $payload['speaker_name'] != '') {
                $query->where('speaker_name', 'like', '%' . $payload['speaker_name'] . '%');
            }

            if (isset($payload['meeting_type']) && $payload['meeting_type'] != '') {
                $query->where('meeting_type', $payload['meeting_type']);
            }

            if (isset($payload['create_time']) && $payload['create_time'] != '') {
                $query->whereDate('create_time',  $payload['create_time']);
            }
        };

        $data = $this->deleteTime()->with(
            [
                'speakerDetail' => function ($query) {
                    $query->select('id', 'name', 'title');
                }
            ]
        )->where($where)
            ->select(
                'id',
                'project_id',
                'project_meeting_id',
                'speaker_id',
                'speaker_name',
                'speaker_email',
                'meeting_type',
                'invitation_status',
                'invitation_remarks',
                'audit_time',
                // 'is_display',
                'create_time',
                'is_fill_info',
            )
            ->orderBy('create_time', 'desc')
            ->paginate($payload['limit'])
            ->toArray();

        $data = ['total' => $data['total'], 'current_page' => $data['current_page'], 'page_size' => $data['per_page'], 'pages' => ceil($data['total'] / $data['per_page']), 'list' => $data['data'], 'meeting_name' => ProjectMeeting::find($project_meeting_id)->meeting_name];
        return $data;
    }


    /**
     * 获取邀请阶段详情
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-11-25
     * @param [type] $id
     * @return void
     */
    public function getSpeakerInvitationDetail($id)
    {
        $info = $this->deleteTime()->with(
            [
                'emailRecord' => function ($query) {
                    $query->select('id', 'project_meeting_invitation_speaker_id', 'email_type', 'send_email_remarks', 'reply_email_remarks', 'to_email_time', 'create_time', 'update_time')->where('is_display', 1)->whereIn('email_type', [1, 2, 3])->orderBy('create_time', 'desc');
                },
                'speakerDetail' => function ($query) {
                    $query->select('id', 'name', 'title');
                }
            ]
        )->where('id', $id)
            ->where('is_display', 1)
            ->select(
                'id',
                'project_id',
                'project_meeting_id',
                'speaker_id',
                'speaker_name',
                'speaker_email',
                'meeting_type',
                'meeting_remarks',
                'create_time',
            )
            ->first();

        $data['info'] = $info;
        $data['project_name'] = ProjectMeeting::find($info->project_meeting_id)->meeting_name;
        return $data;
    }

    /**
     * 获取沟通阶段详情
     *
     * @Author hechengwei
     * @email chichiooo@sina.com
     * @DateTime 2024-11-25
     * @param [type] $id
     * @return void
     */
    public function getSpeakerConnectDetail($id, $meetingType)
    {
        $reload = [];
        if ($meetingType == 1) {
            $reload = [
                // 线上会议信息+测试需求给到讲者
                'meetingOnline' => function ($query) {
                    $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'conference_number', 'password', 'conference_url', 'conference_is_display', 'is_test', 'test_time', 'test_is_display', 'is_display')->where('is_display', 1)->whereNull('delete_time');
                },
                // 可编辑日程翻译给讲者
                'meetingSchedule' => function ($query) {
                    $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'is_display', 'schedule_url')
                        ->where('is_display', 1);
                },
                // 会后感谢信
                'meetingAfter' => function ($query) {
                    $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'is_display')->where('is_display', 1);
                },
            ];
        } elseif ($meetingType == 2) {
            $reload = [
                // 签证计划
                'visaProgram' => function ($query) {
                    $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'is_display')->where('is_display', 1);
                },
                // 签证包发送
                'visaPackage' => function ($query) {
                    $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'is_display', 'file_name', 'visa_url')->where('is_display', 1);
                },
                // 邀请函
                'invitationLetter' => function ($query) {
                    $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'is_display', 'letter_url')->where('is_display', 1);
                },
                // 邀请函邮寄发单号
                'invitationLetterNumber' => function ($query) {
                    $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'is_display', 'letter_number')->where('is_display', 1);
                },
                // @todo  更新外讲表  eb_speaker_resume  的常旅客号
                // 索要常旅客号
                'frequentFlyer' => function ($query) {
                    $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'is_display', 'frequent_flyer_number')->where('is_display', 1);
                },
                // 发讲者喜好问卷
                'meetingQuestions' =>  function ($query) {
                    $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'is_display', 'file_name', 'questionnaire_url')->where('is_display', 1);
                },
                // 行程终版+国际机票+酒店信息给讲者
                'finalTravel' =>  function ($query) {
                    $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'is_display', 'final_travel', 'ticket', 'hotel')->where('is_display', 1);
                },
                // 索要中国签证扫描页
                'chinaVsiaScan'  => function ($query) {
                    $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'is_display', 'scan_url')->where('is_display', 1);
                },
                // 接机安排和温馨提示等
                'pickUpAndTips'  => function ($query) {
                    $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'is_display')->where('is_display', 1);
                },
                // 活动感谢信+询问报销+出入境章扫描件
                'scan'  => function ($query) {
                    $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'is_display', 'thank_you', 'reimbursement', 'scan')->where('is_display', 1);
                },
                // 索要报销单据扫描件
                'reimbursement'  => function ($query) {
                    $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'is_display', 'reimbursement_url')->where('is_display', 1);
                },
                // 发结算单
                'statement' => function ($query) {
                    $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'is_display', 'final_statement_url')->where('is_display', 1);
                },
            ];
        }



        $info = $this->deleteTime()->with(array_merge([
            // 特殊邮件记录
            'emailRecord' => function ($query) {
                $query->select(
                    'id',
                    'project_meeting_invitation_speaker_id',
                    'email_type',
                    'send_email_remarks',
                    'reply_email_remarks',
                    'to_email_time',
                    'create_time',
                    'update_time'
                )
                    ->where('is_display', 1)
                    ->where('email_type', 4)
                    ->whereNull('delete_time')
                    ->orderBy('create_time', 'desc');
            },
            // 关联资料上传表
            'uploadData' => function ($query) {
                $query->select(
                    'id',
                    'meeting_type',
                    'invitation_speaker_id',
                    'meeting_time_bj',
                    'meeting_time_en',
                    'meeting_start_time',
                    'meeting_end_time',
                    'service_charge',
                    'payer',
                    'meeting_info_is_display',
                    'meeting_materiel',
                    'meeting_screen_recording',
                    'meeting_photo',
                    'meeting_materiel_is_display',
                    'meeting_ppt',
                    'meeting_ppt_is_display'
                )->whereNull('delete_time');
                // ->where('meeting_info_is_display', 1)
                // ->where('meeting_materiel_is_display', 1)
                // ->where('meeting_ppt_is_display', 1);
            },
            // // cuc账户录入
            // 'cuc' => function ($query) {
            //     $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'is_display')
            //         ->where('is_display', 1)
            //         ->whereNull('delete_time');
            // },
            // // 劳务费支付
            // 'charge' => function ($query) {
            //     $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'is_display')
            //         ->where('is_display', 1)
            //         ->whereNull('delete_time');;
            // },
            // @todo  如果外讲库的讲者简历、护照、付款信息有的话用那俩表的eb_speaker_passport、eb_speaker_resume
            // 索要讲者简历+照片
            'speakerResume' => function ($query) {
                $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'is_display', 'speaker_resume', 'speaker_resume_translate', 'speaker_avatar')
                    ->where('is_display', 1)
                    ->whereNull('delete_time');
            },
            // @todo  更新 eb_speaker_resume表付款信息
            // 索要讲者付款信息+护照
            'speakerPassport' => function ($query) {
                $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'is_display', 'remittance', 'surname', 'name', 'birth', 'passport_number', 'nationality', 'sex', 'place_of_birth', 'passport_issuing_place', 'validity', 'upload_passport')
                    ->where('is_display', 1)
                    ->whereNull('delete_time');
            },
            // 签订协议
            'signAgreement' => function ($query) {
                $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'is_display', 'sign_agreement_url')
                    ->where('is_display', 1)
                    ->whereNull('delete_time');
            },
            // PPT要求及返回截止日期
            'pptReturnDeadline' => function ($query) {
                $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'is_display', 'ppt_return_deadline')
                    ->where('is_display', 1)
                    ->whereNull('delete_time');
            },
            // PPT跟进
            'pptFollowUp' => function ($query) {
                $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'is_display', 'ppt_follow_up')
                    ->where('is_display', 1)
                    ->whereNull('delete_time');
            },
            // 翻译PPT给到讲者
            'pptTranslate' => function ($query) {
                $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'is_display', 'ppt_translate')->where('is_display', 1)->whereNull('delete_time');
            },
            // 翻译讨论问题给讲者
            'translateToLecturer' => function ($query) {
                $query->select('id', 'invitation_speaker_id', 'status', 'remarks', 'is_display', 'ppt_translate_problem')->where('is_display', 1)->whereNull('delete_time');
            },
            // 关联讲者
            'speaker' => function ($query) {
                // 关联讲者护照、简历
                $query->with([
                    'resume',
                    'passport'
                ])
                    ->select('id', 'institution', 'institution_cn', 'institution_en', 'photo', 'title', 'name')
                    ->where('status', 1)
                    ->whereNull('delete_time');
            },

        ], $reload))->where('id', $id)
            ->where('is_display', 1)
            ->whereNull('delete_time')
            ->select(
                'id',
                'project_id',
                'project_meeting_id',
                'speaker_id',
                'speaker_name',
                'speaker_email',
                'meeting_type',
                'meeting_remarks',
                'create_time',
            )
            ->first();

        return $info;
    }
}
